package org.elucid.gadmin;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.elucid.gadmin.model.ColumnItem;
import org.elucid.gadmin.model.DatabaseItem;
import org.elucid.gadmin.model.TableItem;

public class GConnection {
	
	private final Logger log = Logger.getLogger(GConnection.class);
	private ServerInfo serverInfo;
	private Properties prop;

	public GConnection(ServerInfo serverInfo) throws IOException {
		super();
		this.serverInfo = serverInfo;

		switch (serverInfo.type) {
		case postgresql: {
			prop = new Properties();
			// Relative paths (those without a leading '/') in getResource()/getResourceAsStream() mean that the resource will be searched relative to the directory which
			// represents the package the class is in.
			InputStream in = getClass().getResourceAsStream("commands_pg.properties");
			prop.load(in);
			in.close();
		}
			;
			break;
		}
	}

	/**
	 * 
	 * @param query
	 * @param database
	 * @return
	 * @throws Exception
	 */
	public ResultSet query(String query, String database) throws Exception {
		
		log.debug(String.format("query(), DB=", database ));
		
		Connection con = null;
		Properties connectionProps = new Properties();
		connectionProps.put("user", serverInfo.username);
		connectionProps.put("password", serverInfo.password);
		String url = "jdbc:" + serverInfo.type.name() + "://" + serverInfo.ip_address + ":" + serverInfo.port + "/";
		if (database != null)
			url += database;
		con = DriverManager.getConnection(url, connectionProps);
		Statement stmt = con.createStatement();
		stmt.execute(query);
		ResultSet rs = stmt.getResultSet();

		return rs;
	}

	/**
	 * 
	 * @return
	 * @throws Exception
	 */
	public List<DatabaseItem> readDatabaseList() throws Exception {
		List<DatabaseItem> databases = new ArrayList<DatabaseItem>();

		String query = (String) prop.get("LIST_DATABASES");
		ResultSet rs = (ResultSet) query(query, null);
		int i = 0 ;
		while (rs.next()) {
			databases.add( new DatabaseItem( rs.getString(1) ) );
			i++;
		}

		log.debug(String.format("readDatabaseList(), resultCount=%s", i ));
		
		return databases;
	}

	/**
	 * 
	 * @param databaseName
	 * @return
	 * @throws Exception
	 */
	public Map<TableItem, List<ColumnItem>> readTableList(String databaseName) throws Exception {
		
		Map<TableItem, List<ColumnItem>> tables = new HashMap<TableItem, List<ColumnItem>>();
		
		String query = (String) prop.get("LIST_TABLENAMES");
		ResultSet rsTables = (ResultSet) query(query, databaseName);
		int i = 0;
		while (rsTables.next()) {
			TableItem tableName = new TableItem( rsTables.getString(1) );
			
			List<ColumnItem> columns = new ArrayList<ColumnItem>();
			String queryColumns = String.format( prop.get("LIST_COLUMNS").toString(), tableName ) ;
			ResultSet rsColumns = (ResultSet) query(queryColumns, databaseName);
			while( rsColumns.next() )
			{
				String columnName = rsColumns.getString(1);
				columns.add( new ColumnItem( columnName  ) );
			}
			rsColumns.getStatement().getConnection().close();
			rsColumns.getStatement().close();
			rsColumns.close();
			
			
			tables.put(tableName, columns );
			i++;
		}
		
		rsTables.getStatement().getConnection().close();
		rsTables.getStatement().close();
		rsTables.close();
		
		log.debug(String.format("readTableList(), resultCount=%s", i ));

		return tables;
	}

}
